package tako.wisers.com.android.apis.database;

import java.io.File;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.io.output.ByteArrayOutputStream;

import com.google.android.maps.GeoPoint;

import tako.wisers.com.android.apis.parser.DBXMLParser;
import tako.wisers.com.android.apis.parser.xml.item.Column;
import tako.wisers.com.android.apis.parser.xml.item.Table;
import tako.wisers.com.android.apis.util.BitmapUtils;
import tako.wisers.com.android.apis.util.Global;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.graphics.Bitmap;
import android.graphics.Bitmap.CompressFormat;
import android.util.Log;

public class SQListManager extends SQLiteOpenHelper {
	private static SQListManager mSQLMng;
	private DBXMLParser mDBParser;
	private final static String DB_NAME = "tako_DB";
	private final static String GEO_LAT_TAG = "GEOPOINT_LAT";
	private final static String GEO_LON_TAG = "GEOPOINT_LON";
	public final static boolean TABLE_INMEMORY = true;
	public final static boolean TABLE_INDATABASE = false;
	private final SQLiteDatabase mMemoryDataBase = SQLiteDatabase.create(null);
	private final static String VARCHAR_CHARSET = "UTF-8";

	/**
	 * eg:CREATE TABLE IF NOT EXISTS " + _TABLE_NAME + " (username VARCHAR,
	 * password VARCHAR," + " country VARCHAR, email VARCHAR);"
	 */
	private final static String CREATE_TABLE = "CREATE TABLE IF NOT EXISTS %s (%s);";

	private final static String DROP_TABLE = "DROP TABLE IF EXISTS %s";

	// /**
	// * INSERT INTO " + _TABLE_NAME + " (username, password, country, email)" +
	// "
	// * VALUES ('Gramlich', 'Nicolas', 'Germany', 'Gramlich@mymobisoft.com');
	// */
	// private final static String INSERT_DATA = "INSERT INTO %s (%s) VALUES
	// (%s);";

	private SQListManager(Context ctx) {
		super(ctx, DB_NAME, null, 1);
	}

	public static void build(Context ctx) {
		if (mSQLMng == null) {
			mSQLMng = new SQListManager(ctx);
		}
	}

	public SQLiteDatabase getConnection(boolean inMemory) {
		if (inMemory) {
			return mMemoryDataBase;
		}
		return this.getWritableDatabase();
	}

	public static SQListManager getInstance() {
		return mSQLMng;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	}

	public void createTable(boolean inMemory) {
		mDBParser = DBXMLParser.getInstance();
		SQLiteDatabase db = getConnection(inMemory);
		Iterator<Table> itr = mDBParser.getTableMap().values().iterator();
		db.beginTransaction();
		while (itr.hasNext()) {
			Table table = itr.next();
			if (!table.isInMemory() == inMemory) {
				continue;
			}
			if (!table.isInMemory() && needAlterTable(table, db)) {
				File downFile = new File(Global.DOWNLOAD_CONFIG_PATH);
				downFile.delete();
				String sql = String.format(DROP_TABLE, table.getName());
				db.execSQL(sql);
			}
			String params = "";
			for (Column column : table.getColumnList()) {
				String name = column.getName();
				String type = column.getType();
				if (type.equalsIgnoreCase("gps")) {
					params += GEO_LAT_TAG + " INTEGER, " + GEO_LON_TAG
							+ " INTEGER, ";
				} else {
					params += name + " " + type + ", ";
				}
			}
			params = params.substring(0, params.lastIndexOf(","));
			String sql = String.format(CREATE_TABLE, table.getName(), params);
			db.execSQL(sql);
		}
		db.setTransactionSuccessful();
		db.endTransaction();
	}

	private boolean needAlterTable(Table table, SQLiteDatabase db) {
		if (!tableExists(db, table.getName())) {
			return false;
		}
		Cursor cursor = db.query(table.getName(), null, "1 == 0", null, null,
				null, null);
		List<String> colimnNameList = table.getColumnNameList();
		try {
			if (colimnNameList.size() == cursor.getColumnCount()) {
				String[] columns = cursor.getColumnNames();
				for (String column : columns) {
					if (!colimnNameList.contains(column)) {
						return true;
					}
				}
				return false;
			}
		} catch (Exception e) {
			Log.e("Cursor", e.getMessage());
		} finally {
			cursor.close();
		}
		return true;
	}

	@SuppressWarnings("unchecked")
	public void insert(List<?> objList, boolean inMemory) {
		SQLiteDatabase db = getConnection(inMemory);
		if (objList == null || objList.size() == 0) {
			return;
		}
		Class cls = objList.get(0).getClass();
		Table table = mDBParser.getTable(cls.getName());
		List<String> columnNameList = table.getColumnNameList();
		Iterator itr = objList.iterator();
		db.beginTransaction();
		while (itr.hasNext()) {
			Object obj = itr.next();
			db.insert(table.getName(), null, getJdoContentValues(
					columnNameList, obj));
		}
		db.setTransactionSuccessful();
		db.endTransaction();
	}

	@SuppressWarnings("unchecked")
	public void update(List<?> objList, String filte, boolean inMemory) {
		SQLiteDatabase db = getConnection(inMemory);
		if (objList == null || objList.size() == 0) {
			return;
		}
		Class cls = objList.get(0).getClass();
		Table table = mDBParser.getTable(cls.getName());
		String tableName = table.getName();
		List<String> columnNameList = table.getColumnNameList();
		Iterator itr = objList.iterator();
		db.beginTransaction();
		while (itr.hasNext()) {
			Object obj = itr.next();
			ContentValues values = getJdoContentValues(columnNameList, obj);
			int index = db.update(tableName, values, filte, null);
			if(index == 0){
				db.insert(tableName, null, values);
			}
		}
		db.setTransactionSuccessful();
		db.endTransaction();
	}

	@SuppressWarnings("unchecked")
	/**
	 * keywords 的放入顺序需要和xml的key先后顺序相同
	 */
	public List select(Class cls, boolean inMemory, String filte) {
		SQLiteDatabase db = getConnection(inMemory);
		Table table = mDBParser.getTable(cls.getName());
		List<Column> tempList = table.getColumnList();
		Iterator<Column> itr = tempList.iterator();
		List<String> columnList = new ArrayList<String>();
		while (itr.hasNext()) {
			Column column = itr.next();
			if (column.getType().equalsIgnoreCase("gps")) {
				columnList.add(GEO_LAT_TAG);
				columnList.add(GEO_LON_TAG);
			} else {
				columnList.add(column.getName());
			}
		}
		
		String[] columns = new String[columnList.size()];
		columnList.toArray(columns);
		if(filte != null && filte.equals("")) filte = null;
		Cursor mCursor = db.query(table.getName(), columns, filte, null, null, null, null);
		return getSelectList(mCursor, cls, table.getColumnList());
	}

	public void close() {
		this.close();
		mMemoryDataBase.close();
	}

	private boolean tableExists(SQLiteDatabase db, String tableName) {
		// SELECT count(*) FROM sqlite_master WHERE type='table' and name='%s';
		String[] columns = { "count(*)" };
		Cursor tableCountCursor = db.query("sqlite_master", columns,
				"type='table' and name='" + tableName + "'", null, null, null,
				null);
		try {
			if (tableCountCursor.moveToFirst()) {
				int count = tableCountCursor.getInt(0);
				return count > 0;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			tableCountCursor.deactivate();
		}
		return false;
	}

	private List<?> getSelectList(Cursor mCursor, Class<?> cls,
			ArrayList<Column> columns) {
		List<Object> Items = new ArrayList<Object>();
		try {
			while (mCursor.moveToNext()) {
				Object item = cls.newInstance();
				for (Column column : columns) {
					if (column == null)
						continue;
					if (column.getType().equalsIgnoreCase("VARCHAR")) {
						setVarchar(mCursor, item, column);
					} else if (column.getType().equalsIgnoreCase("BLOB")) {
						setBitMap(mCursor, item, column);
					} else if (column.getType().equalsIgnoreCase("gps")) {
						setLocation(mCursor, item, column);
					} else if (column.getType().equalsIgnoreCase("INTEGER")) {
						setInt(mCursor, item, column);
					}
				}
				Items.add(item);
			}
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		} finally {
			mCursor.close();
		}
		return Items;
	}

	public int clearTable(String tableName, boolean inMemory) {
		SQLiteDatabase conn = getConnection(inMemory);
		return conn.delete(tableName, null, null);
	}

	private void setInt(Cursor mCursor, Object item, Column column)
			throws IllegalAccessException {
		int columnIndex = mCursor.getColumnIndex(column.getName());
		setFieldValue(item, mCursor.getInt(columnIndex), column.getName(), item
				.getClass());
	}

	private void setVarchar(Cursor mCursor, Object item, Column column)
			throws IllegalAccessException {
		int columnIndex = mCursor.getColumnIndex(column.getName());
		String str = mCursor.getString(columnIndex);
		try {
			if (str != null) {
				str = URLDecoder.decode(str, VARCHAR_CHARSET);
			}
		} catch (UnsupportedEncodingException e) {
			Log.e("UnsupportedEncodingException", e.getMessage());
		}
		setFieldValue(item, str, column.getName(), item.getClass());
	}

	private void setBitMap(Cursor mCursor, Object item, Column column)
			throws IllegalAccessException {
		int columnIndex = mCursor.getColumnIndex(column.getName());
		Bitmap bitmap = new BitmapUtils().loadBitmap(mCursor.getBlob(columnIndex));
		setFieldValue(item, bitmap, column.getName(), item.getClass());
	}

	private void setLocation(Cursor mCursor, Object item, Column column)
			throws IllegalAccessException {
		int latIndex = mCursor.getColumnIndex(GEO_LAT_TAG);
		int lonIndex = mCursor.getColumnIndex(GEO_LON_TAG);
		int lat = mCursor.getInt(latIndex);
		int lon = mCursor.getInt(lonIndex);
		GeoPoint point = new GeoPoint(lat, lon);
		setFieldValue(item, point, column.getName(), item.getClass());
	}

	private void setFieldValue(Object item, Object value, String fieldName,
			Class<?> cls) throws IllegalArgumentException,
			IllegalAccessException, SecurityException {
		try {
			Field field = cls.getDeclaredField(fieldName);
			field.setAccessible(true);
			field.set(item, value);
		} catch (NoSuchFieldException e) {
			setFieldValue(item, value, fieldName, cls.getSuperclass());
		}
	}

	private ContentValues getJdoContentValues(List<String> columnName,
			Object obj) {
		ContentValues value = new ContentValues();
		Iterator<String> itr = columnName.iterator();
		while (itr.hasNext()) {
			String fieldName = itr.next();
			setFieldValue(obj, value, fieldName, obj.getClass());
		}
		return value;
	}

	private void setFieldValue(Object obj, ContentValues value,
			String fieldName, Class<?> cls) {
		try {
			Field field = cls.getDeclaredField(fieldName);
			field.setAccessible(true);
			Object fieldValue = field.get(obj);
			if (fieldValue instanceof String) {
				String str = (String) fieldValue;
				if (str != null) {
					str = URLEncoder.encode(str, VARCHAR_CHARSET);
				}
				value.put(fieldName, str);
			} else if (fieldValue instanceof Integer) {
				value.put(fieldName, (Integer) fieldValue);
			} else if (fieldValue instanceof Double) {
				value.put(fieldName, (Double) fieldValue);
			} else if (fieldValue instanceof Boolean) {
				value.put(fieldName, (Boolean) fieldValue);
			} else if (fieldValue instanceof Long) {
				value.put(fieldName, (Long) fieldValue);
			} else if (fieldValue instanceof Float) {
				value.put(fieldName, (Float) fieldValue);
			} else if (fieldValue instanceof Bitmap) {
				putBitMap(value, fieldName, fieldValue);
			} else if (fieldValue instanceof GeoPoint) {
				value.put(GEO_LAT_TAG, ((GeoPoint) fieldValue).getLatitudeE6());
				value
						.put(GEO_LON_TAG, ((GeoPoint) fieldValue)
								.getLongitudeE6());
			}
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
			setFieldValue(obj, value, fieldName, cls.getSuperclass());
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
	}

	private void putBitMap(ContentValues value, String fieldName,
			Object fieldValue) {
		try {
			Bitmap bitmap = (Bitmap) fieldValue;
			ByteArrayOutputStream outStream = new ByteArrayOutputStream();
			bitmap.compress(CompressFormat.PNG, 100, outStream);
			outStream.flush();
			outStream.close();
			value.put(fieldName, outStream.toByteArray());
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}
